package com.yc.hwc.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.Query;

public interface ITaskLogExDao extends ITaskLogDao{
	
	@Query(nativeQuery = true, 
			value = "SELECT\n" +
					"	a.`name` taskName,\n" +
					"	date_format(a.in_time,'%m-%d %H:%i') taskInTime,\n" +
					"	date_format(a.finish_at,'%m-%d %H:%i') finishAt,\n" +
					"	a.intro,\n" +
					"	b.`name` userName,\n" +
					"	a.id taskId,\n" +
					"	b.id inUser\n" +
					"FROM\n" +
					"	task a\n" +
					"JOIN user b ON a.cls_sn = b.cls_sn\n" +
					"LEFT JOIN task_log c ON a.id = c.task_id\n" +
					"AND b.id = c.in_user\n" +
					"WHERE ifnull(b.id,0) = ?1"
					+ " AND case ?2"
					+ "		when -1 then c.id is null"
					+ "		when -2 then c.id is not null"
					+ "		when 0  then 1=1"
					+ "		else c.id=?2"
					+ "		end")
	public List<Map<String, Object>> findTaskLog(long uid, long taskLogId);
	
	@Query(nativeQuery = true, 
			value = "SELECT\n" +
					"	a.`name` taskName,\n" +
					"	date_format(a.in_time,'%y-%m-%d %H:%i') taskInTime,\n" +
					"	date_format(a.finish_at,'%y-%m-%d %H:%i') finishAt,\n" +
					"	b.`NAME` userName,\n" +
					"	b.cls_sn clsSn,\n" +
					"	a.id taskId,\n" +
					"	c.*\n" +
					"FROM\n" +
					"	task a\n" +
					"JOIN user b ON a.cls_sn = b.cls_sn\n" +
					"LEFT JOIN task_log c ON a.id = c.task_id\n" +
					"AND b.id = c.in_user\n" +
					"WHERE a.cls_sn = ?1 and (a.id = ?2 or ?2 = 0)\n"
					+ "ORDER BY a.id, b.id, c.id")
	public List<Map<String, Object>> findByClsSnAndTaskId(String clsSn, int taskId);

}
